﻿using CallCenter.Data.Base;
using CallCenter.Data.BizUtilities.Base;
using CallCenter.Data.Models;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CallCenter.Data.BizUtilities
{
  public  class CommEmployeeUtility:BaseUtility
    {
        public CommEmployeeUtility(DataManager manager)
            :base(manager)
        { }
        public int Insert(int commId, int empId, DateTime date)
        {
            int nRtn = -1;

          string sql= "Insert INTO ccCommEmployee " +
                "(commId, employeeId, date) VALUES(@commId, @employeeId, @date)  SELECT @pResult =@@IDENTITY ";
            using (var cmd = this.Factory.GetCommand(sql))
            {
                this.Factory.AddParameter(cmd, "@commId", SqlDbType.Int, 10, commId);
                this.Factory.AddParameter(cmd, "@employeeId", SqlDbType.Int, 10, empId);
                this.Factory.AddParameter(cmd, "@date", SqlDbType.DateTime, 10, date);
              
                var param = this.Factory.AddParameter(cmd, "@pResult", System.Data.SqlDbType.NVarChar, 500, null);
                param.Direction = System.Data.ParameterDirection.Output;

                this.Factory.ExecuteNonQuery(cmd);
                nRtn = Convert.ToInt32(param.Value);
            }


            return nRtn; //retruning the data stored in the table
        }
    
        public int Update(int id, int commId, int contactid, DateTime date)
        {
            int nRtn = -1;
            string sql = "UPDATE ccCommEmployee  SET" +
                        " commId=@commId, contactId=@contactId, date=@date HWERE ";
            using (var cmd = this.Factory.GetCommand(sql))
            {
                this.Factory.AddParameter(cmd, "@id", SqlDbType.Int, 10, id);
                this.Factory.AddParameter(cmd, "@commId", SqlDbType.Int, 10, commId);
                this.Factory.AddParameter(cmd, "@contactId", SqlDbType.Int, 10, contactid);
                this.Factory.AddParameter(cmd, "@date", SqlDbType.DateTime, 10, date);
               return this.Factory.ExecuteNonQuery(cmd);
            }
        }

          
          //  return nRtn; //retruning the data stored in the table
        
        public DataTable GetAll()
        {
            string sql =  "SELECT * FROM ccCommEmployee";

        return this.Factory.ExecuteTable(sql);


    }
        public DataTable GetAllGrid(int commId)
        {
            string sql  = "select cmc.*, cc.lastName+', '+cc.firstName as employee from ccCommEmployee cmc " +
                " INNER JOIN ddlEmployees e on e.id = cmc.employeeId  " +
                " INNER JOIN ccContacts cc on cc.id =  e.contactId " +
                " WHERE cmc.commId= " + commId;

            return this.Factory.ExecuteTable(sql);

        }
        public DataTable GetEmp()
        {
            string sql = "select cmc.*, cc.lastName+', '+cc.firstName as employee from ccCommEmployee cmc " +
                " INNER JOIN ddlEmployees e on e.id = cmc.employeeId  " +
                " INNER JOIN ccContacts cc on cc.id =  e.contactId ";


            return this.Factory.ExecuteTable(sql);

        }

        public string GetEmpEmail(int empId)
        {
            string sql = @"select Distinct cc.email from ccCommEmployee cmc
             INNER JOIN ddlEmployees e on e.id = cmc.employeeId  
             INNER JOIN ccContacts cc on cc.id =  e.contactId 
                WHERE e.id = " + empId;
            return Convert.ToString(this.Factory.ExecuteScalar(sql));

        }
        public void Delete(int commId) //delete the row of data using the id passed to the function
        {
           string sql = "DELETE FROM ccCommEmployee " +
                " WHERE commId = @commId ";

            using (var cmd = this.Factory.GetCommand(sql))
                    {

                       this.Factory.AddParameter(cmd, "@commId", SqlDbType.Int, 4,commId);
                       this.Factory.ExecuteNonQuery(cmd);
                   }
        }

        public bool HavingccCommEmployeeByCompanyIDandEmpId(int CompanyId, int EmployeeId)
        {
            string sql = "SELECT * FROM ccCommEmployee";

            return true;
        }

    }
}
